package approval.dao;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import models.util.MySqlDataAccessHelper;

import resource.pojo.ListReference;

public class ListReferenceDAO {
	public static boolean insert(ArrayList<ListReference> lst){
		int a = 0;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = "CALL sp_insertListReference(?,?,?,?,?)";
		CallableStatement cs = null;
		try {
			mysql.open();
			cs = mysql.getConnection().prepareCall(sql);
			for (ListReference l : lst) {
				cs.setString(1,  new Date().getTime()+"");
				cs.setString(2, l.getId_emp());
				cs.setString(3, l.getId_mana());
				cs.setString(4, l.getId_group());
				cs.setInt(5,l.getIdStatus());
				a += cs.executeUpdate();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			mysql.close();
			try {
				cs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return a == lst.size();
	}
	
	public static boolean update(String id,String group){
		int a = 0;
		MySqlDataAccessHelper mysql  = new MySqlDataAccessHelper();
		String sql = "CALL sp_updateReference(?,?)";
		CallableStatement cs = null;
		try {
			mysql.open();
			cs = mysql.getConnection().prepareCall(sql);
			cs.setString(1,id);
			cs.setString(2, group);
			a = cs.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
			try {
				cs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return a>0;
	}

	public static ArrayList<ListReference> getReference(String idGroup){
		ArrayList<ListReference> lst = new ArrayList<ListReference>();
		MySqlDataAccessHelper mysql  = new MySqlDataAccessHelper();
		String sql =  "CALL sp_getReferenceByGroup(?)";
		CallableStatement cs = null;
		try {
			mysql.open();
			cs = mysql.getConnection().prepareCall(sql);
			cs.setString(1, idGroup);
			ResultSet rs= cs.executeQuery();
			while(rs.next()){
				lst.add(new ListReference(rs.getString("id_employee"),
						rs.getString("id_manager"),
						rs.getString("id_group"),
						rs.getInt("id_status")));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return lst;
	}
}
